Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

Controlling the size of a transaction

You’ve already learned which statements start a transaction automatically. To summarize, these are:

FOR EACH blocks that directly update the database.
REPEAT blocks that directly update the database.
Procedure blocks that directly update the database.
DO blocks with the ON ERROR or ON ENDKEY qualifiers (which you’ll learn more about later) that contain statements that update the database.

You can also control the size of a transaction by adding the TRANSACTION keyword to a DO, FOR EACH, or REPEAT block. This can force a transaction to be larger, but because of the statements that start a transaction automatically, you cannot use it to make a transaction smaller than Progress would otherwise make it.

Take another look at the update procedure saveOrder, in the sample logic procedure orderlogic.p, to see how transaction blocks are affected by changes to the procedure.

As written, there is a DO TRANSACTION block around the whole update of both the Order and any modified OrderLines:

DO TRANSACTION ON ERROR UNDO, LEAVE:   DO:          /* Order update block */   END.    FOR EACH ttOline:           /* OrderLine update block */    END. END.  /* END Transaction block. */

The update of the Order and its OrderLines happens as a single transaction. If any errors are encountered in any of the updates, the entire transaction is backed out.

To verify this, you can generate a listing file as you’ve done earlier:

When you run this COMPILE statement, your listing file tells you, among other things, where all the transactions begin and end. This is very valuable information. You should always use a listing file in any complex procedure to make sure that your record scope and transaction scope are as you intended.

Taking a look at this listing file, you can see that the DO TRANSACTION block is a top-level block within its procedure, marked with a 1. The DO block inside it, where the Order is actually updated, is block level 2:

144   1    DO TRANSACTION ON ERROR UNDO, LEAVE:     145   1       FIND ttOrder WHERE ttOrder.TransType = "" NO-ERROR.     146   1       IF AVAILABLE (ttOrder) THEN            /* If this rec is there then the Order was updated on the client. */     147   1                 148   2      DO:

Further down, you can see that the FOR EACH block that operates on the OrderLines is also a nested block, at level 2 within the main DO TRANSACTION block:

164   2     FOR EACH ttOline WHERE ttOline.TransType = "":     165   2          /* Bring the updated version into the other buffer. */

Now if you look at the end of the file, you see a summary of all the blocks. Here’s an excerpt from that part of the listing. It shows that the procedure blocks for the internal procedures fetchOrder and saveOrder are not transaction blocks:

File Name       Line Blk.     Type Tran         Blk. Label -------------------- ---- --------- ---- -------------------------------- ...ter8\orderlogic.p   82 Procedure No   Procedure fetchOrder ...ter8\orderlogic.p  111 For       No ...ter8\orderlogic.p  124 Procedure No   Procedure saveOrder      Buffers: bUpdateOrder               sports2000.Order

This is a good thing. You never want your transactions to default to the level of a procedure, because they are likely to be larger than you want them to be. This means that record locks are held longer than necessary and that more records might be involved in a transaction than you intended.

Next you see that Progress identifies the first DO block at line 144 as a transaction block. This is because it has an explicit TRANSACTION qualifier on it. The nested DO block two lines later is not a transaction block because a DO block by itself does not mark a transaction.

The FOR EACH block at line 164 is also marked as a transaction block:

...ter8\orderlogic.p  144 Do        Yes ...ter8\orderlogic.p  146 Do        No ...ter8\orderlogic.p  151 Do        No ...ter8\orderlogic.p  156 Do        No ...ter8\orderlogic.p     164 For       Yes     Buffers: sports2000.OrderLine              bUpdateOline

What does this mean? Is this really a separate transaction? The answer is no, because the FOR EACH block is nested inside the larger DO TRANSACTION block. This code tells you that the FOR EACH block would be a transaction block (because this is the nature of FOR EACH blocks that perform updates). However, because it is nested inside a larger transaction, it becomes a subtransaction. Progress can back out changes made in a subtransaction within a larger transaction when an error occurs, and you can also do this yourself, as you’ll learn a little later in the "Subtransactions" section.